-- SELECT * FROM FN_LOAD_LOTS_FOR_NEW_TEST_RELEASES(  46, 8, 8, 'FT', 'PROD' ) ORDER BY PS_TYPE_ID DESC, ISNULL( EXPECTED_DATE_OUT, GETDATE())
ALTER FUNCTION FN_LOAD_LOTS_FOR_NEW_TEST_RELEASES
(
	@RELEASE_PROCESSSTEP_ID INT,
	@FROM_VENDOR_ID			INT,
	@TO_VENDOR_ID 			INT,
	@RELEASE_PS_TYPE		VARCHAR(50),
	@RELEASE_TYPE			VARCHAR(50)
)
RETURNS @RESULTS TABLE( ROW_ID INT IDENTITY, BASEPRODUCT_NAME VARCHAR(50), FROM_PART_NUMBER VARCHAR(50), TO_PART_NUMBER VARCHAR(50), FROM_PROCESSSTEP_ID INT, TO_PROCESSSTEP_ID INT, FROM_VENDOR_ID VARCHAR(50), 
						FROM_VENDOR_NAME VARCHAR(50), TO_VENDOR_ID VARCHAR(50), TO_VENDOR_NAME VARCHAR(50), PS_TYPE VARCHAR(50), PS_TYPE_ID INT, LOT_ID INT, 
						LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50), QTY_AVAILABLE INT, DATE_IN DATETIME, EXPECTED_DATE_OUT DATETIME, IS_HOLD INT, IS_ENG INT, IS_RMA INT, QTY_PULLED INT )
AS
BEGIN
	DECLARE @WAFERIDS_CURSOR 			CURSOR
	DECLARE @PULLED_WAFER_IDS_TABLE		TABLE ( WAFER_ID INT )
	DECLARE @RELEASED_LOT_NUMBERS_TABLE	TABLE ( LOT_NUMBER VARCHAR(50) )
	DECLARE @PATH_PROCESSSTEP_IDS_TABLE	TABLE ( PROCESSSTEP_ID INT )
	DECLARE @PULLED_WAFER_IDS 			VARCHAR(100)
	DECLARE @CONCAT_WAFERIDS_INTO_STRING 	VARCHAR(100)

	DECLARE @LOT_CURSOR 			CURSOR
	DECLARE @PRODUCT_CURSOR			CURSOR
	DECLARE @LOT_NUMBER 			VARCHAR(50)
	DECLARE @ROW_ID 				INT
	DECLARE @QTY_AVAILABLE 			INT
	DECLARE @QTY_PULLED 			INT
	DECLARE @QTY_SCRAPPED 			INT
	DECLARE @ADAPTOR_QTY_SCRAPPED 	INT
	DECLARE @OPERATIONAL_QTY		INT
	DECLARE @BASE_PRODUCT_NAME		VARCHAR(50)
	DECLARE @SOURCE_PART_NUMBER		VARCHAR(50)
	DECLARE @RELEASE_PART_NUMBER	VARCHAR(50)
	DECLARE @RELEASE_VENDOR_NAME	VARCHAR(50)
	DECLARE @TEMP_RELEASE_PS_TYPE	VARCHAR(50)
	DECLARE @IS_HOLD 				INT
	DECLARE @IS_ENG 				INT
	DECLARE @IS_RMA 				INT
	DECLARE @FROM_LOCATION_ID 		INT
	DECLARE @FAB_PROCESSSTEP_ID 	INT
	DECLARE @SOURCE_PROCESSSTEP_ID 	INT
	DECLARE @PROCESSSTEP_ID 		INT
	DECLARE @CURRENT_PROCESS 		VARCHAR(50) 
	DECLARE @CURRENT_VENDOR 		VARCHAR(50)
	DECLARE @CURRENT_PROCESS_ID 	VARCHAR(50)
	DECLARE @CURRENT_PROCESS_EXP_DATE_OUT	VARCHAR(50)
	DECLARE @PS_TYPE_ID				VARCHAR(50)
	DECLARE @RELEASE_PS_TYPE_ID		VARCHAR(50)
	DECLARE @FROM_VENDOR_NAME		VARCHAR(50)
	DECLARE @DIE_QUANTITY			INT
	DECLARE @LOT_ID					INT
	
	SET @TEMP_RELEASE_PS_TYPE =  @RELEASE_PS_TYPE
	
	SET @RELEASE_PS_TYPE_ID = 3

	--SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID AND V.VENDOR_ID = @TO_VENDOR_ID AND L_TYPE = @TEMP_RELEASE_PS_TYPE

	SELECT @FROM_LOCATION_ID = LOCATION_ID, @FROM_VENDOR_NAME = V_NAME FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID AND V.VENDOR_ID = @FROM_VENDOR_ID AND L_TYPE = 'ASSEMBLY'

	SELECT @SOURCE_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID( @RELEASE_PROCESSSTEP_ID ) WHERE PS_TYPE = 'ASSEMBLY'

	SELECT @SOURCE_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @SOURCE_PROCESSSTEP_ID
	SELECT @RELEASE_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @RELEASE_PROCESSSTEP_ID
	
	SELECT TOP 1 @BASE_PRODUCT_NAME = P1.PART_NUMBER FROM PRODUCT P1, PRODUCT P2 WHERE P1.PRODUCT_ID = P2.P_PARENT_PRODUCT_ID AND P2.PROCESSSTEP_ID = @RELEASE_PROCESSSTEP_ID

	SELECT TOP 1 @RELEASE_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = @TO_VENDOR_ID

	INSERT INTO @RESULTS ( BASEPRODUCT_NAME, FROM_PART_NUMBER, TO_PART_NUMBER, FROM_PROCESSSTEP_ID, TO_PROCESSSTEP_ID, FROM_VENDOR_ID, FROM_VENDOR_NAME, TO_VENDOR_ID, TO_VENDOR_NAME, PS_TYPE, PS_TYPE_ID, LOT_ID, LOT_NUMBER, FAB_LOT_NUMBER, QTY_AVAILABLE, DATE_IN, EXPECTED_DATE_OUT ) 
		SELECT DISTINCT 
			( @BASE_PRODUCT_NAME 	) AS BASE_PRODUCT_NAME, 
			( @SOURCE_PART_NUMBER	) AS FROM_PART_NUMBER,
			( @RELEASE_PART_NUMBER 	) AS TO_PART_NUMBER, 
			( @SOURCE_PROCESSSTEP_ID	) AS FROM_PROCESSSTEP_ID, 	/* source processstepid */
			( @RELEASE_PROCESSSTEP_ID 	) AS TO_PROCESSSTEP_ID, 	/* release processstepid */
			( @FROM_VENDOR_ID 			) AS FROM_VENDOR_ID,
			( @FROM_VENDOR_NAME 		) AS FROM_VENDOR_NAME,
			( @TO_VENDOR_ID 			) AS TO_VENDOR_ID,
			( @RELEASE_VENDOR_NAME 		) AS TO_VENDOR_NAME,
			PS_TYPE,
			1 AS PS_TYPE_ID,
			LT.LOT_ID,
			LT.LOT_NUMBER,
			LT.FAB_LOT_NUMBER,
			LT.QTY_IN, 
			LT.DATE_IN,
			( SELECT EXPECTED_DATE_OUT FROM ASSEMBLY_LOT WHERE ASSEMBLY_LOT.LOT_ID = LT.LOT_ID ) AS EXPECTED_DATE_OUT
		FROM LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS, LOCATION L 
		WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOCATION_ID = L.LOCATION_ID AND 
		LT.LOT_CURRENT_STATUS <> 'Close' AND LOT_SPLIT <> 0 AND L.LOCATION_ID = @FROM_LOCATION_ID AND LOT_CATEGORY = @RELEASE_TYPE AND P.PROCESSSTEP_ID =  @SOURCE_PROCESSSTEP_ID
	
		UNION 
			SELECT DISTINCT 
				( @BASE_PRODUCT_NAME 	) AS BASE_PRODUCT_NAME, 
				( @SOURCE_PART_NUMBER	) AS FROM_PART_NUMBER,
				( @RELEASE_PART_NUMBER 	) AS TO_PART_NUMBER, 
				( @SOURCE_PROCESSSTEP_ID	) AS FROM_PROCESSSTEP_ID, 	/* source processstepid */
				( @RELEASE_PROCESSSTEP_ID 	) AS TO_PROCESSSTEP_ID, 	/* release processstepid */
				( @FROM_VENDOR_ID 			) AS FROM_VENDOR_ID,
				( @FROM_VENDOR_NAME 		) AS FROM_VENDOR_NAME,
				( @TO_VENDOR_ID 			) AS TO_VENDOR_ID,
				( @RELEASE_VENDOR_NAME 		) AS TO_VENDOR_NAME,
				PS_TYPE,
				2 AS PS_TYPE_ID,		
				LT.LOT_ID,
				LT.LOT_NUMBER,
				LT.FAB_LOT_NUMBER,
				SHIP_QTY,
				Ship_DATE,
				NULL --EXPECTED_DATE_OUT
			FROM SHIPPED_LOT SL, LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS, LOCATION L WHERE SL.LOT_ID = LT.LOT_ID AND 
				LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID  AND LT.LOCATION_ID = L.LOCATION_ID AND LOT_SPLIT <> 0 
			AND SL.STATUS = 'TRANSIT' AND L.LOCATION_ID = @FROM_LOCATION_ID AND LOT_CATEGORY = @RELEASE_TYPE AND P.PROCESSSTEP_ID =  @SOURCE_PROCESSSTEP_ID

	SET @LOT_CURSOR = CURSOR FOR SELECT ROW_ID, LOT_ID, LOT_NUMBER, QTY_AVAILABLE, PS_TYPE_ID FROM @RESULTS WHERE QTY_AVAILABLE > 0
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @ROW_ID, @LOT_ID, @LOT_NUMBER, @QTY_AVAILABLE, @PS_TYPE_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF( @RELEASE_PS_TYPE = 'FT' ) 
		BEGIN
			SELECT @QTY_PULLED = SUM( ISNULL( QTY_PULLED, 0 ) ) FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TEST_REL_STATUS <> 'CANCELLED' AND TRLT.LOT_ID = @LOT_ID
			--SELECT @QTY_PULLED = SUM( ISNULL( QTY_PULLED, 0 ) ) FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TEST_REL_STATUS <> 'CANCELLED' AND TRLT.LOT_NUMBER = @LOT_NUMBER
		END

		SELECT @IS_HOLD = ( CASE WHEN COUNT(*) > 0  THEN 0 ELSE 1 END  ) FROM LOT_TABLE WHERE ( LOT_CURRENT_STATUS = 'Hold' OR LOT_MANUAL_HOLD = 0 ) AND LOT_SPLIT <> 0 AND FAB_LOT_NUMBER = @LOT_NUMBER
		SELECT @IS_ENG = ( CASE WHEN COUNT(*) > 0  THEN 0 ELSE 1 END  ) FROM LOT_TABLE WHERE Lot_Category = 'ENGG' AND LOT_SPLIT <> 0 AND FAB_LOT_NUMBER = @LOT_NUMBER
		SELECT @IS_RMA = ( CASE WHEN COUNT(*) > 0  THEN 0 ELSE 1 END  ) FROM LOT_TABLE WHERE Lot_Category = 'RMA' AND LOT_SPLIT <> 0 AND FAB_LOT_NUMBER = @LOT_NUMBER
	
		SET @QTY_AVAILABLE = @QTY_AVAILABLE - ISNULL( @QTY_PULLED, 0 ) 

		UPDATE @RESULTS SET QTY_AVAILABLE = @QTY_AVAILABLE, QTY_PULLED =@QTY_PULLED, IS_HOLD = @IS_HOLD, IS_ENG = @IS_ENG, IS_RMA = @IS_RMA WHERE ROW_ID = @ROW_ID 
	
		SET @QTY_PULLED 	= NULL
		SET @DIE_QUANTITY	= 0

		FETCH NEXT FROM @LOT_CURSOR INTO @ROW_ID, @LOT_ID, @LOT_NUMBER, @QTY_AVAILABLE, @PS_TYPE_ID
	END
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR

	DELETE FROM @RESULTS WHERE QTY_AVAILABLE <= 0 OR QTY_AVAILABLE IS NULL	

	RETURN
END

